-- =============================================
-- This stored procedure figures out some of the calculated values stored in Nav_Menu table
-- for performance improvement.
--
-- Modifications
-- 10/10/2003   E.Tatsui
-- =============================================

CREATE           PROCEDURE amsp_CMUpdateNavProperties
    @InNavMenuID numeric
AS
BEGIN
  DECLARE
    @AncestoryList varchar(255),
    @FilePath varchar(255),
    @DescendantCount numeric,
    @SortOrder numeric(28,18),
    @CategoryDepth numeric,
    @ParentNavMenuID numeric,
    @Name varchar(255),
    @WebsiteKey uniqueidentifier,
    @CurrentAncestoryList varchar(255),
    @CurrentFilePath varchar(255),
    @CurrentDescendantCount numeric,
    @NavMenuID numeric,
    @CurrentParentNavMenuID numeric,
    @AncestorNavMenuID numeric,
    @NavContentGroupInd char(1)

  SELECT @SortOrder = SortOrder,
         @CategoryDepth = CategoryDepth,
         @Name = IsNull(DirectoryName,Name),
         @ParentNavMenuID = ParentNavMenuID,
         @WebsiteKey = WebsiteKey,
         @CurrentAncestoryList = IsNull(AncestoryList,''),
         @CurrentFilePath = IsNull(FilePath,''),
         @CurrentDescendantCount = DescendantCount,
         @AncestorNavMenuID = AncestorNavMenuID,
         @NavContentGroupInd = NavContentGroupInd
    FROM Nav_Menu WITH (NOLOCK)
   WHERE NavMenuID = @InNavMenuID

  SET @CurrentParentNavMenuID = @ParentNavMenuID

  -- First get descendant count.
  IF @NavContentGroupInd = 'N'
    SELECT @DescendantCount = Count(*)
      FROM Nav_Menu z
      WITH (NOLOCK)
    WHERE z.WebsiteKey = @WebsiteKey
      AND z.SortOrder > @SortOrder
      AND z.WorkflowStatusCode <> 'D'
      AND z.NavContentGroupInd = 'N'
      AND z.SortOrder <
         (SELECT IsNull(Min(x.SortOrder),999999999)
            FROM Nav_Menu x
             WITH (NOLOCK)
           WHERE x.SortOrder > @SortOrder
             AND x.CategoryDepth <= @CategoryDepth
             AND x.WebsiteKey = @WebsiteKey
             AND z.NavContentGroupInd = 'N')
  ELSE
    SELECT @DescendantCount = Count(*)
      FROM Nav_Menu z
      WITH (NOLOCK)
    WHERE z.NavContentGroupInd = 'C'
      AND z.WorkflowStatusCode <> 'D'
      AND z.SortOrder > @SortOrder
      AND z.SortOrder <
         (SELECT IsNull(Min(x.SortOrder),999999999)
            FROM Nav_Menu x
             WITH (NOLOCK)
           WHERE x.SortOrder > @SortOrder
             AND x.CategoryDepth <= @CategoryDepth
             AND x.NavContentGroupInd = 'C')

  -- Next, get file path and ancestory list.
  SET @FilePath = @Name + '/'
  SET @AncestoryList = ''
  WHILE @ParentNavMenuID IS NOT NULL BEGIN
    SET @AncestoryList = '''' + convert(varchar(10), @ParentNavMenuID) + ''',' + @AncestoryList

    SELECT @Name = IsNull(DirectoryName,Name),
           @ParentNavMenuID = ParentNavMenuID
      FROM Nav_Menu WITH (NOLOCK)
     WHERE NavMenuID = @ParentNavMenuID

    IF @@RowCount > 0
      SET @FilePath = @Name + '/' + @FilePath
    ELSE
      BREAK  
  END -- End while loop

  -- Strip out the last ","
  IF Len(@AncestoryList) > 0
    SET @AncestoryList = LEFT(@AncestoryList, Len(@AncestoryList) - 1)

  IF @CurrentAncestoryList <> @AncestoryList
     OR @CurrentFilePath <> @FilePath
     OR @CurrentDescendantCount <> @DescendantCount BEGIN
    UPDATE Nav_Menu
       SET FilePath = @FilePath,
           AncestoryList = NULLIF(@AncestoryList,''),
           DescendantCount = @DescendantCount
     WHERE NavMenuID = @InNavMenuID
  END

  IF @CurrentFilePath <> @FilePath BEGIN
    -- Replace filepath for all the children.
    UPDATE Nav_Menu
       SET FilePath = @FilePath + SUBSTRING(FilePath,LEN(@CurrentFilePath)+1, LEN(FilePath)- LEN(@CurrentFilePath))
     WHERE AncestorNavMenuID = @AncestorNavMenuID
       AND NavMenuID <> @InNavMenuID
       AND CHARINDEX(@CurrentFilePath, FilePath) = 1
  END

  -- If ancestory list or descendant count changes, the item's parents should
  -- be updated also.
  IF @CurrentParentNavMenuID IS NOT NULL
     AND (@CurrentAncestoryList <> @AncestoryList
          OR @CurrentDescendantCount <> @DescendantCount) BEGIN
    EXEC amsp_CMUpdateNavProperties @CurrentParentNavMenuID
  END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMUpdateNavProperties] TO [IMIS]
GO